#!/usr/bin/python3
# -*- coding:utf-8 -*-
# @who:  spacelacc
# @date: 2025/04/09
# @func: data crawler
# @ver:  V1

import os
import re
import xlrd
#from customconstant     import CustomConstant
from PySide6.QtCore     import QObject, Signal

class CrawlerForSpreadsheet():
    def __init__(self):
        self.epsilon = 0.0000001
        self.columns = [3, 7, 11, 13, 15, 17, 19, 21]
        self.pattern = re.compile(r'\d{2}\d{2}\d{3}')
        # list stored coupling result
        self.fully = []
        self.fuzzy = []
        self.fails = []
        # list contains all probes' value
        self.assembler = []

    def parse(self, files:list) -> None:
        # No file given, job done and return
        if not files:
            return

        # valid files found, clear old parsed data
        self.assembler.clear()

        # File given, extracting values
        for file in files:
            try:
                sheet = xlrd.open_workbook(file).sheets()[0]
                for row in range(sheet.nrows):
                    serial = str(sheet.cell_value(row, 0))
                    if not self.pattern.match(serial):
                        continue

                    # We check for repeat here
                    serial = serial[:-2]
                    found_copy = False
                    for i in range(len(self.assembler)):
                        element = self.assembler[i]
                        if serial == element[0]:
                            found_copy = True
                            break
                    if found_copy:
                        continue

                    newline = []
                    # First 4 important parameters
                    newline.append(serial)   # serial
                    newline.append(True)     # if selected
                    newline.append(0)        # source kind
                    newline.append(file)     # source addr

                    # Second 8 important parameters
                    for col in self.columns:
                        data = sheet.cell_value(row,col)/1000000
                        newline.append(float(data))
#                       newline.append(sheet.cell_value(row,col)/1000000)

                    # calculate slopes and evaluators
                    difference = 0
                    ev_sign = 0
                    ev_fully = 0   # evaluator fully
                    ev_fuzzy = 0   # evaluator fuzzy
                    sr_fully = 0   # squares fully
                    sr_fuzzy = 0   # squares fuzzy

                    #!!!! this line may be altered afterwards
                    for d in range(6, len(newline)):
                        difference = newline[d] - newline[d-1]
                        if difference > self.epsilon:
                            ev_sign = 2
                        elif difference < -self.epsilon:
                            ev_sign = 1
                        else:
                            ev_sign = 0
                        
                        # Calculating for 'fully'
                        ev_fully = 10 * ev_fully + ev_sign
                        sr_fully += difference ** 2

                        # Calculating for 'fuzzy'
                        if (d > 6):
                            ev_fuzzy = 10 * ev_fuzzy + ev_sign
                            sr_fuzzy += difference ** 2
                    
                    # Last 4 evaluators and least squares
                    newline.append(ev_fuzzy)
                    newline.append(ev_fully)
                    newline.append(sr_fuzzy * 10000)
                    newline.append(sr_fully * 10000)

                    # add this newline to 'queue'
                    self.assembler.append(newline)
            except xlrd.XLRDError:
                continue
            except OSError:
                continue

        # Deal with repeated serials


    def getParsedData(self):
        return self.assembler

    def getparseddata(self):
        return self.assembler


class CrawlerForSpreadsheet1(QObject):

    signalProbesCoupled = Signal(list, list, list)

    def __init__(self):
        super().__init__()

        self.epsilon = 0.0000001
        self.columns = [3, 7, 11, 13, 15, 17, 19, 21]
        self.pattern = re.compile(r'\d{2}\d{2}\d{3}')

        # these two used for updating 'treeview'
        # but in bulk mode of major changes
        self.lastpos = 0
        self.currpos = 0

        # list stored coupling result
        self.fully = []
        self.fuzzy = []
        self.fails = []

        # this used for 'minor change'
        self.modified = []

        # list contains all probes' value
#       self.probestocouple = []
        self.assembler = []

    def parse(self, files:list) -> None:
        # No file given, job done and return
        if not files:
            return

        # Upate size pointer for updating 'treeview'
        self.lastpos = self.currpos

        # Variable for recording parsing process
        # If this equals 0, no need for updating pointer.
        recorder = 0

        # File given, extracting values
        for file in files:
            try:
                sheet = xlrd.open_workbook(file).sheets()[0]
                for row in range(sheet.nrows):
                    serial = str(sheet.cell_value(row, 0))
                    if not self.pattern.match(serial):
                        continue

                    # We check for repeat here
                    value = serial[:-2]
                    found_copy = False
                    for i in range(len(self.assembler)):
                        element = self.assembler[i]
                        if value == element[0]:
                            found_copy = True
                            break
                    if found_copy:
                        continue

                    newline = []
                    newline.append(value)         # value
                    newline.append(CustomConstant.ItemText)
                    newline.append(True)          # if selected
                    newline.append(CustomConstant.ItemFromFile)
                    newline.append(file)          # source
                    for col in self.columns:
                        newline.append(sheet.cell_value(row,col)/1000000)

                    # calculate slopes and evaluators
                    difference = 0
                    ev_sign = 0
                    ev_fully = 0   # evaluator fully
                    ev_fuzzy = 0   # evaluator fuzzy
                    sr_fully = 0   # squares fully
                    sr_fuzzy = 0   # squares fuzzy

                    #!!!! this line may be altered afterwards
                    for d in range(6, len(newline)):
                        difference = newline[d] - newline[d-1]
                        if difference > self.epsilon:
                            ev_sign = 2
                        elif difference < -self.epsilon:
                            ev_sign = 1
                        else:
                            ev_sign = 0
                        
                        # Calculating for 'fully'
                        ev_fully = 10 * ev_fully + ev_sign
                        sr_fully += difference ** 2

                        # Calculating for 'fuzzy'
                        if (d > 6):
                            ev_fuzzy = 10 * ev_fuzzy + ev_sign
                            sr_fuzzy += difference ** 2
                    
                    newline.append(ev_fuzzy)
                    newline.append(ev_fully)
                    newline.append(sr_fuzzy * 10000)
                    newline.append(sr_fully * 10000)

                    # add this newline to 'queue'
                    self.assembler.append(newline)
                    # size add 1, used for updating 'treeview'
                    recorder += 1

            except xlrd.XLRDError:
                continue
            except OSError:
                continue

        # Deal with repeated serials

        # check for 'recorder', if it is greater than 0, 
        # then we need to do update.
        self.currpos += recorder


#   def parse(self, files: list, assembler: list) -> None:
    def parse(self, files:list) -> None:
        # No file given, job done and return
        if not files:
            return

        # Upate size pointer for updating 'treeview'
        self.lastpos = self.currpos

        # Variable for recording parsing process
        # If this equals 0, no need for updating pointer.
        recorder = 0

        # File given, extracting values
        for file in files:
            try:
                sheet = xlrd.open_workbook(file).sheets()[0]
                for row in range(sheet.nrows):
                    serial = str(sheet.cell_value(row, 0))
                    if not self.pattern.match(serial):
                        continue

                    # We check for repeat here
                    value = serial[:-2]
                    found_copy = False
                    for i in range(len(self.assembler)):
                        element = self.assembler[i]
                        if value == element[0]:
                            found_copy = True
                            break
                    if found_copy:
                        continue

                    newline = []
                    newline.append(value)         # value
#                   newline.append(CustomConstant.ItemText)
                    newline.append(True)          # if selected
#                   newline.append(CustomConstant.ItemFromFile)
                    newline.append(file)          # source
                    for col in self.columns:
                        newline.append(sheet.cell_value(row,col)/1000000)

                    # calculate slopes and evaluators
                    difference = 0
                    ev_sign = 0
                    ev_fully = 0   # evaluator fully
                    ev_fuzzy = 0   # evaluator fuzzy
                    sr_fully = 0   # squares fully
                    sr_fuzzy = 0   # squares fuzzy

                    #!!!! this line may be altered afterwards
                    for d in range(6, len(newline)):
                        difference = newline[d] - newline[d-1]
                        if difference > self.epsilon:
                            ev_sign = 2
                        elif difference < -self.epsilon:
                            ev_sign = 1
                        else:
                            ev_sign = 0
                        
                        # Calculating for 'fully'
                        ev_fully = 10 * ev_fully + ev_sign
                        sr_fully += difference ** 2

                        # Calculating for 'fuzzy'
                        if (d > 6):
                            ev_fuzzy = 10 * ev_fuzzy + ev_sign
                            sr_fuzzy += difference ** 2
                    
                    newline.append(ev_fuzzy)
                    newline.append(ev_fully)
                    newline.append(sr_fuzzy * 10000)
                    newline.append(sr_fully * 10000)

                    # add this newline to 'queue'
                    self.assembler.append(newline)
                    # size add 1, used for updating 'treeview'
                    recorder += 1

            except xlrd.XLRDError:
                continue
            except OSError:
                continue

        # Deal with repeated serials

        # check for 'recorder', if it is greater than 0, 
        # then we need to do update.
        self.currpos += recorder

    def newparsed(self):
        if self.lastpos == self.currpos:
            return []
        # set 'lastpos' same as 'currpos'
        posStart = self.lastpos
        self.lastpos = self.currpos
        return self.assembler[posStart:]

    def removeBySource(self, source):
        if source == '' or not source:
            return
        length = len(self.assembler)
        for i in range(length-1, -1, -1):
            line = self.assembler[i]
            if line[CustomConstant.ItemSourceAddrPos] == source:
                self.assembler.pop(i)
        # update pos pointer
        self.currpos = len(self.assembler)
        self.lastpos = self.currpos

    def execute(self):
        # reset coupling result
        self.fully.clear()
        self.fuzzy.clear()
        self.fails.clear()

        probes_been_coupled = []
        eval_exact_coupled = []
####
        fails_to_couple = []   # probes cannot been exactly coupled
####
        count = -1
        for probe in self.assembler:
            count += 1

            # if probe not selected, just skipt it
            if not probe[CustomConstant.ItemBoolPos]:
                continue
            # if probe has been coupled, skip it
            if probe in probes_been_coupled:
                continue

            for candidate in self.assembler[count+1:]:
                # if probe not selected, skipt it
                if not candidate[CustomConstant.ItemBoolPos]:
                    continue
                # if probe has been coupled, skip it
                if candidate in probes_been_coupled:
                    continue

                if probe[-3] == candidate[-3]:
                    eval_exact_coupled.append(candidate)

            # no couple found, probe cannot couple
            if not eval_exact_coupled:
                # not in fails, add it in
                if probe not in self.fails:
#                   self.fails.append(probe)
                    fails_to_couple.append(probe)

                # Go for next probe
                continue

            # Possible couples has been found, then
            # we should select the best one
            exact_eval = 1000.0
            exact_fit  = eval_exact_coupled[0]
            for candidate in eval_exact_coupled[1:]:
                if probe[-1] < exact_eval:
                    exact_fit  = candidate
                    exact_eval = candidate[-1]
            # Best couple found, add this pair in
            probes_been_coupled.append(probe)
            probes_been_coupled.append(exact_fit)
            if probe not in self.fully:
                self.fully.append(probe)
            if exact_fit not in self.fully:
                self.fully.append(exact_fit)

            # clear for next couple process
            eval_exact_coupled.clear()

        # Starting for fuzzy couples
        probes_been_coupled = []
        could_not_coupled = []
        index = -1
#       for c1 in self.fails:
        for c1 in fails_to_couple:
            index += 1
            eval_fuzzy_coupled = []
            # not participate in coupling
            if not c1[CustomConstant.ItemBoolPos]:
                continue
            # has already been coupled
            if c1 in probes_been_coupled:
                continue

#           for c2 in self.fails[index+1:]:
            for c2 in fails_to_couple[index+1:]:
                if not c2[CustomConstant.ItemBoolPos]:
                    continue
                if c2 in probes_been_coupled:
                    continue

                # fuzzy evaluator match
                if c1[-4] == c2[-4]:
                    eval_fuzzy_coupled.append(c2)

            # check for couple candidates
            if not eval_fuzzy_coupled:
                if c1 not in could_not_coupled:
                    could_not_coupled.append(c1)
                continue

            # search for best couple
            fuzzy_eval = 1000.0
            fuzzy_fit = eval_fuzzy_coupled[0]
            for c in eval_fuzzy_coupled[1:]:
                if c[-2] < fuzzy_eval:
                    fuzzy_fit  = c
                    fuzzy_eval = c[-2]
            probes_been_coupled.append(c1)
            probes_been_coupled.append(fuzzy_fit)
            if c1 not in self.fuzzy:
                self.fuzzy.append(c1)
            if fuzzy_fit not in self.fuzzy:
                self.fuzzy.append(fuzzy_fit)

        # All left probes in 'could_not_coupled' fails
        for probe in could_not_coupled:
            self.fails.append(probe)

        
        # emit signal to notify 'printer'
        self.signalProbesCoupled.emit(self.fully, self.fuzzy, self.fails)

